package com.szit.test.dao.impl;

import com.szit.test.dao.MySqlConnection;
import com.szit.test.dao.SaleDao;
import com.szit.test.entity.PageIndexer;
import com.szit.test.entity.Sale;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 教师数据访问层实现类
 *
 * @author 勇哥
 * @version 1.0 2025-05-15
 */
public class SaleDaoImpl implements SaleDao {
    private MySqlConnection db;

    public SaleDaoImpl(){
        db = new MySqlConnection();
    }

    @Override
    public boolean addTeacher(Sale t) {
        // 1.定义SQL语句：添加一条记录
        String sql = "INSERT INTO sales VALUES(?,?,?,?,?,?,?)";

        // 2.将输入的参数值添加到集合中
        Object[] parameters = {t.getId(),t.getName(),t.getIsreceived(),t.getSdate(),t.getPrice(),t.getPhoto(),t.getCategoryid()};

        // 3.调用通用连接类
        boolean flag = db.execute(sql, parameters);

        return flag;
    }

    @Override
    public boolean updateTeacher(Sale t) {
        // 1.定义SQL语句：添加一条记录
        String sql = "UPDATE sales SET name=?,isreceived=?,sdate=?,price=?,photo=?,categoryid=? WHERE id=?";

        // 2.将输入的参数值添加到集合中
        Object[] parameters = {t.getName(),t.getIsreceived(),t.getSdate(),t.getPrice(),t.getPhoto(),t.getCategoryid(),t.getId()};

        // 3.调用通用连接类
        boolean flag = db.execute(sql, parameters);

        return flag;
    }

    @Override
    public boolean deleteTeacher(int id) {
        // 1.定义SQL语句：添加一条记录
        String sql = "DELETE FROM sales WHERE id=?";

        // 2.将输入的参数值添加到集合中
        Object[] parameters = {id};

        // 3.调用通用连接类
        boolean flag = db.execute(sql, parameters);

        return flag;
    }

    @Override
    public List<Sale> getAllTeachers() {
        List<Sale> teachers = new ArrayList<Sale>();

        // 1.定义SQL语句：添加一条记录
        String sql = "SELECT * FROM sales";

        // 2.调用通用连接类
        ResultSet rs = db.getResultSet(sql, null);

        try {
            // 3. 将结果集数据倒存入到集合中
            while (rs.next()) {
                // 将每条记录存到实体对象中
                Sale t = new Sale();
                t.setId(rs.getInt("id"));
                t.setName(rs.getString("name"));
                t.setIsreceived(rs.getBoolean("isreceived"));
                t.setSdate(rs.getString("sdate"));
                t.setPrice(rs.getDouble("price"));
                t.setPhoto(rs.getString("photo"));
                t.setCategoryid(rs.getInt("categoryid"));

                // 优化籍贯对象
                t.setProvince(new CategoryDaoImpl().getProvinceById(t.getCategoryid()));

                // 将实体对象添加到集合中
                teachers.add(t);
            }

            // 关闭对象
            db.close();
        }catch (SQLException e){
            e.printStackTrace();
        }

        return teachers;
    }

    @Override
    public Sale getTeacherById(int id) {
        Sale t = null;

        // 1.定义SQL语句：添加一条记录
        String sql = "SELECT * FROM sales WHERE id=?";

        // 2.定义参数集合
        Object[]parameters = {id};

        // 3.调用通用连接类
        ResultSet rs = db.getResultSet(sql, parameters);

        try {
            // 4. 将结果集数据倒存入到集合中
            if (rs.next()) {
                // 将每条记录存到实体对象中
                t = new Sale();
                t.setId(rs.getInt("id"));
                t.setName(rs.getString("name"));
                t.setIsreceived(rs.getBoolean("isreceived"));
                t.setSdate(rs.getString("sdate"));
                t.setPrice(rs.getDouble("price"));
                t.setPhoto(rs.getString("photo"));
                t.setCategoryid(rs.getInt("categoryid"));

                // 优化籍贯对象
                t.setProvince(new CategoryDaoImpl().getProvinceById(t.getCategoryid()));
            }
            // 关闭对象
            db.close();
        }catch (SQLException e){
            e.printStackTrace();
        }

        return t;
    }

    @Override
    public List<Sale> getTeachersByPage(PageIndexer page) {
        // 1.定义集合
        List<Sale> lists = new ArrayList<Sale>();

        try {
            // 2.定义SQL语句
            String sql = "select * from sales limit ?,?";

            // 定义参数集合
            Object[]parameters = {(page.getPageIndex() - 1) * page.getPageSize(),page.getPageSize()};

            // 3.调用通用连接类的查询方法

            ResultSet rs = db.getResultSet(sql, parameters);

            // 4.将结果集的数据转存到集合中
            while (rs.next()) {
                // 5.定义实体对象
                Sale t = new Sale();
                t.setId(rs.getInt("id"));
                t.setName(rs.getString("name"));
                t.setIsreceived(rs.getBoolean("isreceived"));
                t.setSdate(rs.getString("sdate"));
                t.setPrice(rs.getDouble("price"));
                t.setPhoto(rs.getString("photo"));
                t.setCategoryid(rs.getInt("categoryid"));

                // 优化籍贯对象
                t.setProvince(new CategoryDaoImpl().getProvinceById(t.getCategoryid()));

                // 把实体对象添加到集合中
                lists.add(t);
            }

            // 4.关闭对象
            db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 计算总页数
        page.setPageCount((int) Math.ceil(getAllTeachers().size() * 1.0 / page.getPageSize()));

        return lists;
    }

    @Override
    public List<Sale> getTeachersByIf(Sale teacher, String isreceived) {
        // 1.定义集合
        List<Sale> lists=new ArrayList<Sale>();

        try {
            // 2.定义SQL语句
            String sql="select * from sales where 1=1 ";

            // 定义参数集合
            List parameters = new ArrayList();

            // 条件拼接
            if(isreceived != null && isreceived.equals("true")) {
                sql+="and isreceived=1 ";
            } else if(isreceived != null && isreceived.equals("false")){
                sql+="and isreceived=0 ";
            }

            if(teacher.getId() != 0) {
                sql+="and id like ? ";
                parameters.add("%"+teacher.getId()+"%");
                if(teacher.getName() != null && teacher.getName() !="") {
                    sql+="and name like ?";
                    parameters.add("%" + teacher.getName() + "%");
                }
            } else {
                if(teacher.getName() != null && teacher.getName() !="") {
                    sql+="and name like ?";
                    parameters.add("%" + teacher.getName() + "%");
                }
            }

            System.out.println(sql);

            // 3.调用通用连接类的查询方法
            Object []pp = null;
            if(parameters.size() > 0){
                pp = parameters.toArray();
            }
            ResultSet rs = db.getResultSet(sql,pp);

            // 4.将结果集的数据转存到集合中
            while (rs.next()) {
                // 5.定义实体对象
                Sale t = new Sale();
                t.setId(rs.getInt("id"));
                t.setName(rs.getString("name"));
                t.setIsreceived(rs.getBoolean("isreceived"));
                t.setSdate(rs.getString("sdate"));
                t.setPrice(rs.getDouble("price"));
                t.setPhoto(rs.getString("photo"));
                t.setCategoryid(rs.getInt("categoryid"));

                // 优化籍贯对象
                t.setProvince(new CategoryDaoImpl().getProvinceById(t.getCategoryid()));

                // 把实体对象添加到集合中
                lists.add(t);
            }

            // 4.关闭对象
            db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return lists;
    }

    @Override
    public List<Sale> getTeachersByPage(Sale teacher, String isreceived, PageIndexer page) {
        // 1.定义集合
        List<Sale> lists = new ArrayList<Sale>();

        try {
            // 2.定义SQL语句
            String sql = "select * from sales where 1=1 ";

            // 定义参数集合
            List parameters = new ArrayList();

            // 条件拼接
            if (isreceived != null && isreceived.equals("true")) {
                sql += "and isreceived=1 ";
            } else if (isreceived != null && isreceived.equals("false")) {
                sql += "and isreceived=0 ";
            }

            if (teacher.getId() != 0) {
                sql += "and id like ? ";
                parameters.add("%" + teacher.getId() + "%");
                if (teacher.getName() != null && teacher.getName() != "") {
                    sql += "and name like ?";
                    parameters.add("%" + teacher.getName() + "%");
                }
            } else {
                if (teacher.getName() != null && teacher.getName() != "") {
                    sql += "and name like ?";
                    parameters.add("%" + teacher.getName() + "%");
                }
            }

            // 拼接分页
            sql += " limit ?,?";
            // 添加参数集合
            parameters.add((page.getPageIndex() - 1) * page.getPageSize());
            parameters.add(page.getPageSize());

            System.out.println(sql);

            // 3.调用通用连接类的查询方法
            Object[] pp = null;
            if (parameters.size() > 0) {
                pp = parameters.toArray();
            }
            ResultSet rs = db.getResultSet(sql, pp);

            // 4.将结果集的数据转存到集合中
            while (rs.next()) {
                // 5.定义实体对象
                Sale t = new Sale();
                t.setId(rs.getInt("id"));
                t.setName(rs.getString("name"));
                t.setIsreceived(rs.getBoolean("isreceived"));
                t.setSdate(rs.getString("sdate"));
                t.setPrice(rs.getDouble("price"));
                t.setPhoto(rs.getString("photo"));
                t.setCategoryid(rs.getInt("categoryid"));

                // 优化籍贯对象
                t.setProvince(new CategoryDaoImpl().getProvinceById(t.getCategoryid()));

                // 把实体对象添加到集合中
                lists.add(t);
            }

            // 4.关闭对象
            db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 计算总页数
        page.setPageCount((int) Math.ceil(getTeachersCountByIf(teacher, isreceived) * 1.0 / page.getPageSize()));

        return lists;
    }

    @Override
    public int getTeachersCountByIf(Sale teacher, String isreceived) {
        // 1.定义集合
        List<Sale> lists=new ArrayList<Sale>();

        try {
            // 2.定义SQL语句
            String sql="select * from sales where 1=1 ";

            // 定义参数集合
            List parameters = new ArrayList();

            // 条件拼接
            if(isreceived != null && isreceived.equals("true")) {
                sql+="and isreceived=1 ";
            } else if(isreceived != null && isreceived.equals("false")){
                sql+="and isreceived=0 ";
            }

            if(teacher.getId() != 0) {
                sql+="and id like ? ";
                parameters.add("%"+teacher.getId()+"%");
                if(teacher.getName() != null && teacher.getName() !="") {
                    sql+="and name like ?";
                    parameters.add("%" + teacher.getName() + "%");
                }
            } else {
                if(teacher.getName() != null && teacher.getName() !="") {
                    sql+="and name like ?";
                    parameters.add("%" + teacher.getName() + "%");
                }
            }

            System.out.println(sql);

            // 3.调用通用连接类的查询方法
            Object []pp = null;
            if(parameters.size() > 0){
                pp = parameters.toArray();
            }
            ResultSet rs = db.getResultSet(sql,pp);

            // 4.将结果集的数据转存到集合中
            while (rs.next()) {
                // 5.定义实体对象
                Sale t = new Sale();
                t.setId(rs.getInt("id"));
                t.setName(rs.getString("name"));
                t.setIsreceived(rs.getBoolean("isreceived"));
                t.setSdate(rs.getString("sdate"));
                t.setPrice(rs.getDouble("price"));
                t.setPhoto(rs.getString("photo"));
                t.setCategoryid(rs.getInt("categoryid"));

                // 优化籍贯对象
                t.setProvince(new CategoryDaoImpl().getProvinceById(t.getCategoryid()));

                // 把实体对象添加到集合中
                lists.add(t);
            }

            // 4.关闭对象
            db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return lists.size();
    }
}
